<?php

class AjaxHocVienController extends BaseController {

    public function postDskhoathi() {
        $input = Input::get('id_hoi_dong_thi');
        $results = Khoa_thi::where('HoiDongThi_Id', '=', $input)->get();
        $html = '<option value="">-- Chọn --</option>';
        foreach ($results as $results) {
            $date_db = new DateTime($results->ngay_bat_dau);
            $date_kt = new DateTime($results->ngay_ket_thuc);
            $html .= '<option value="' . $results->id . '">' . $results->ma_khoa_thi;
            $html .= ' (' . $date_db->format("d/m/Y") . ' - ';
            $html .= $date_kt->format("d/m/Y") . ') ';
            $html .= '</option>';
        }
        return $html;
    }

    public function postDsbuoithi() {
        $input = Input::get('id_khoa_thi');
        $results = Buoi_thi::where('KhoaThi_Id', '=', $input)->get();
        $html = '<option value="">-- Chọn --</option>';
        foreach ($results as $results) {
            $html .= '<option value="' . $results->id . '">' . $results->ngay_thi;
            $html .= ' (' . $results->gio_bat_dau . ' - ';
            $html .= $results->gio_ket_thuc . ') ';
            $html .= '</option>';
        }
        return $html;
    }

    public function postReadexcel() {
        //Nhận biến post lên
        $input = Input::get('namefile');
        $inputFileName = "public/admin/upload_excel/server/php/files/" . $input;
        //Đọc file excel
        Excel::load($inputFileName, function($reader) {
            //Khởi tạo ds mã học viên
            $sbd_array = array();
            //Khởi tạo ds mã học viên
            $makhoathi_array = array();
            //Khởi tạo ds mã học viên
            $machungchi_array = array();
            //array file excel 1
            $results_1 = $reader->all();
            //array file excel 2
            $results = $reader->all();

            foreach ($results_1 as $results_1) {
                array_push($sbd_array, $results_1->sbd);
                array_push($makhoathi_array, $results_1->ma_khoa_thi);
                array_push($machungchi_array, $results_1->ma_chung_chi);
            }

            $exist_sbd = DB::table('hoc_viens')->whereIn('sbd', $sbd_array)->select('sbd')->get();
            $exist_kt_hv = DB::table('hoc_viens')
                            ->leftJoin('khoa_this','hoc_viens.KhoaThi_Id','=','khoa_this.id')
                            ->whereIn('ma_khoa_thi', $makhoathi_array)->select('ma_khoa_thi')->get();
            $exist_kt = DB::table('khoa_this')->whereIn('ma_khoa_thi', $makhoathi_array)->select('ma_khoa_thi')->get();
            $exist_cc = DB::table('chung_chis')->whereIn('ma_chung_chi', $machungchi_array)->select('ma_chung_chi')->get();

            $exist_sbd2 = array();
            $exist_kt2 = array();
            $exist_cc2 = array();
            $exist_kt_hv2 = array();

            foreach ($exist_sbd as $sbd) {

                $exist_sbd2[$sbd->sbd] = $sbd->sbd;
            }
            foreach ($exist_kt as $kt) {

                $exist_kt2[$kt->ma_khoa_thi] = $kt->ma_khoa_thi;
            }
            foreach ($exist_cc as $cc) {

                $exist_cc2[$cc->ma_chung_chi] = $cc->ma_chung_chi;
            }

            foreach ($exist_kt_hv as $kt_hv) {

                $exist_kt_hv2[$kt_hv->ma_khoa_thi] = $kt_hv->ma_khoa_thi;
            }

            // var_dump($exist_kt2);
            //in table
            $html = '<table class="table table-bordered" id="dataTable" style="width:100%">';
            $html .= "<tr><td>Mã Học Viên</td>"
                    . "<td>Số Báo Danh</td>"
                    . "<td>Tên Học Viên</td>"
                    . "<td>Ngày Sinh</td>"
                    . "<td>Giới Tính</td>"
                    . "<td>Dân Tộc</td>"
                    . "<td>Sđt</td>"
                    . "<td>Địa Chỉ</td>"
                    . "<td>Quốc Tịch</td>"
                    . "<td>Nơi Sinh</td>"
                    . "<td>Năm Tốt Nghiệp</td>"
                    . "<td>Hình Thức Đào Tạo</td>"
                    . "<td>Mã Khoá Thi</td>"
                    . "<td>Mã Chứng Chỉ</td>"
                    . "<td>Điểm Lý Thuyết</td>"
                    . "<td>Điểm Thực Hành</td>"
                    . "<td>Điểm TB</td>"
                    . "<td>Xếp Loại</td>"
                    . "</tr>";
            $flat_err = 0;

            foreach ($results as $results) {
                if (isset($exist_sbd2[$results->sbd]) && isset($exist_kt_hv2[$results->ma_khoa_thi])) {
                    // $html .= '<td style="background:rgb(255, 255, 189);color:violet">' . $results->ma_khoa_thi . '</td>';
                     $html .= "<tr style='background: red'>";
                    $flat_err++;
                } else {
                     $html .= "<tr>";
                    // $html .= '<td>' . $results->ma_khoa_thi . '</td>';
                }
                $html .= '<td>' . $results->ma_hv . '</td>';
                $html .= '<td>' . $results->sbd . '</td>';
                $html .= '<td>' . $results->ho_hv . ' ' . $results->ten_hv . '</td>';
                $html .= '<td>' . $results->ngay_sinh_hv . '</td>';
                $html .= '<td>' . $results->gioi_tinh_hv . '</td>';
                $html .= '<td>' . $results->dan_toc_hv . '</td>';
                $html .= '<td>' . $results->sdt_hv . '</td>';
                $html .= '<td>' . $results->dia_chi_hv . '</td>';
                $html .= '<td>' . $results->quoc_tich_hv . '</td>';
                $html .= '<td>' . $results->noi_sinh_hv . '</td>';
                $html .= '<td>' . $results->nam_tot_nghiep . '</td>';
                $html .= '<td>' . $results->hinh_thuc_dao_tao . '</td>';

                if (!isset($exist_kt2[$results->ma_khoa_thi])) {
                    $html .= '<td style="background:rgb(255, 255, 189);color:violet">' . $results->ma_khoa_thi . '</td>';
                    $flat_err++;
                } else {
                    $html .= '<td>' . $results->ma_khoa_thi . '</td>';
                }
                if (!isset($exist_cc2[$results->ma_chung_chi])) {
                    $html .= '<td style="background:rgb(255, 255, 189);color:violet">' . $results->ma_chung_chi . '</td>';
                    $flat_err++;
                } else {
                    $html .= '<td>' . $results->ma_chung_chi . '</td>';
                }

                if ($results->diem_lt > 10 || $results->diem_lt < 0) {
                    $html .= '<td style="background:rgb(255, 255, 189);color:green">' . $results->diem_lt . '</td>';
                    $flat_err++;
                } else {
                    $html .= '<td>' . round($results->diem_lt,2) . '</td>';
                }

                if ($results->diem_th > 10 || $results->diem_th < 0) {
                    $html .= '<td style="background:rgb(255, 255, 189);color:green">' . $results->diem_th . '</td>';
                    $flat_err++;
                } else {
                    $html .= '<td>' . round($results->diem_th,2) . '</td>';
                }
                $html .= '<td>' . round($results->diem_tb,2) . '</td>';
                if(trim($results->xep_loai) == ''){
                    $html .= '<td style="background:rgb(255, 255, 189);color:green">' . $results->xep_loai . '</td>';
                    $flat_err++;
                }else{
                   $html .= '<td>' . $results->xep_loai . '</td>'; 
                }
                $html .= "</tr>";

            }
            $html .= "</table>";
            $html .="<div id='flats-err'hidden>" . $flat_err . "</div>";
            // $html ="<div id='flats-err'hidden>" . $flat_err . "</div>";
            // // // if($flat_err!=0){
            // // //     unlink($inputFileName);
            // // // }
            echo $html;
        });
    }

    public function postStoredshocvien() {
        $input = Input::get('namefile');
        $inputFileName = "public/admin/upload_excel/server/php/files/" . $input;

        Excel::load($inputFileName, function($reader) {

            $results = $reader->all();
            foreach ($results as $results) {
                if ($results->xep_loai == 'Kh. đạt') {
                    $ket_qua = 0;
                } else {
                    $ket_qua = 1;
                }
                $id_khoa_thi = DB::table('khoa_this')->where('ma_khoa_thi', '=', $results->ma_khoa_thi)->pluck('id');
                $id_chung_chi = DB::table('chung_chis')->where('ma_chung_chi', '=', $results->ma_chung_chi)->pluck('id');
                $input = array(
                    'ma_hv' => $results->ma_hv,
                    'sbd' => $results->sbd,
                    'ho_hv' => $results->ho_hv,
                    'ten_hv' => $results->ten_hv,
                    'ngay_sinh_hv' => $results->ngay_sinh_hv,
                    'gioi_tinh_hv' => $results->gioi_tinh_hv,
                    'dan_toc_hv' => $results->dan_toc_hv,
                    'email_hv' => $results->email_hv,
                    'sdt_hv' => $results->sdt_hv,
                    'dia_chi_hv' => $results->dia_chi_hv,
                    'quoc_tich_hv' => $results->quoc_tich_hv,
                    'noi_sinh_hv' => $results->noi_sinh_hv,
                    'nam_tot_nghiep' => $results->nam_tot_nghiep,
                    'hinh_thuc_dao_tao' => $results->hinh_thuc_dao_tao,
                    'KhoaThi_Id' => $id_khoa_thi,
                    'ChungChi_Id' => $id_chung_chi,
                    'diem_lt' => round($results->diem_lt,2),
                    'diem_th' => round($results->diem_th,2),
                    'diem_tb' =>round($results->diem_tb,2),
                    'ket_qua' => $results->ket_qua,
                    'xep_loai' => $results->xep_loai,
                    'TK_NhapDiem_Id' => Auth::user()->id,
                    'ket_qua' => $ket_qua
                );
                $id = DB::table('hoc_viens')->insertGetId($input);
            }
        });
    }

    public function getUpdate() {
        return View::make('hoc_viens.update');
    }

    public function postReadexcelupdate() {
        //Nhận biến post lên
        $input = Input::get('namefile');
        $inputFileName = "public/admin/upload_excel/server/php/files/" . $input;
        //Đọc file excel
        Excel::load($inputFileName, function($reader) {
            //Khởi tạo ds số báo danh
            $sbd_array = array();
            //Khởi tạo ds mã học viên
            $makhoathi_array = array();
            //Khởi tạo ds mã học viên
            $mshv_array = array();
            //array file excel 1
            $results_1 = $reader->all();
            //array file excel 2
            $results = $reader->all();

            foreach ($results_1 as $results_1) {
                array_push($mshv_array, $results_1->ma_hv);
                array_push($makhoathi_array, $results_1->ma_khoa_thi);
                array_push($sbd_array, $results_1->sbd);
            }

            $exist_hv = DB::table('hoc_viens')->whereIn('ma_hv', $mshv_array)->select('ma_hv')->get();
            $exist_kt = DB::table('khoa_this')->whereIn('ma_khoa_thi', $makhoathi_array)->select('ma_khoa_thi')->get();
            $exist_sbd = DB::table('hoc_viens')->whereIn('sbd', $sbd_array)->select('sbd')->get();

            $exist_hv2 = array();
            $exist_kt2 = array();
            $exist_sbd2 = array();

            foreach ($exist_hv as $hv) {

                $exist_hv2[$hv->ma_hv] = $hv->ma_hv;
            }
            foreach ($exist_kt as $kt) {

                $exist_kt2[$kt->ma_khoa_thi] = $kt->ma_khoa_thi;
            }
            foreach ($exist_sbd as $sbd) {

                $exist_sbd2[$sbd->sbd] = $sbd->sbd;
            }
            // //in table
            $html = '<table class="table table-striped table-bordered" id="dataTable" style="width:100%">';
            $html .= "<tr><td>Mã Học Viên</td>"
                    . "<td>Số Báo Danh</td>"
                    . "<td>Họ Tên Học Viên</td>"
                    . "<td>Mã Khóa Thi</td>"
                    . "<td>Điểm Lý Thuyết</td>"
                    . "<td>Điểm Thực Hành</td>"
                    . "<td>Điểm Trung Bình</td>"
                    . "<td>Xếp Loại</td>"
                    . "<td>Số Hiệu Bằng</td>"
                    . "<td>Số Sổ Cấp Chứng Chỉ</td>"
                    . "<td>Số Quyết Định Tốt Nghiệp</td>"
                    . "</tr>";
            $flat_err = 0;
            foreach ($results as $results) {
                $html .= "<tr>";
                if (!isset($exist_hv2[$results->ma_hv])) {
                    $html .= '<td style="background:rgb(255, 255, 189);color:red">' . $results->ma_hv . '</td>';
                    $flat_err++;
                } else {
                    $html .= '<td>' . $results->ma_hv . '</td>';
                }
                $html .= '<td>' . $results->sbd . '</td>';
                $html .= '<td>' . $results->ho_hv . ' ' . $results->ten_hv . '</td>';
                $html .= '<td>' . $results->ma_khoa_thi . '</td>';
                $html .= '<td>' . $results->diem_lt . '</td>';
                $html .= '<td>' . $results->diem_th . '</td>';
                $html .= '<td>' . $results->diem_tb . '</td>';
                $html .= '<td>' . $results->xep_loai . '</td>';
                $html .= '<td>' . $results->so_hieu_bang . '</td>';
                $html .= '<td>' . $results->so_so_cap_chung_chi . '</td>';
                $html .= '<td>' . $results->so_quyet_dinh_tot_nghiep . '</td>';
            }
            $html .= "</table>";
            $html .="<div id='flats-err'hidden>" . $flat_err . "</div>";
            // if($flat_err!=0){
            //     unlink($inputFileName);
            // }
            echo $html;
        });
    }

    public function postUpdateshocvien() {
        $input = Input::get('namefile');
        $inputFileName = "public/admin/upload_excel/server/php/files/" . $input;

        Excel::load($inputFileName, function($reader) {

            $results = $reader->all();
            foreach ($results as $results) {
                if ($results->xep_loai == 'Kh. đạt') {
                    $ket_qua = 0;
                } else {
                    $ket_qua = 1;
                }
                $id_khoa_thi = DB::table('khoa_this')->where('ma_khoa_thi', $results->ma_khoa_thi)->pluck('id');
                $input = array(
                    'ma_hv' => $results->ma_hv,
                    'sbd' => $results->sbd,
                    'ho_hv' => $results->ho_hv,
                    'KhoaThi_Id' => $id_khoa_thi,
                    'ten_hv' => $results->ten_hv,
                    'diem_lt' => $results->diem_lt,
                    'diem_th' => $results->diem_th,
                    'diem_tb' => $results->diem_tb,
                    'xep_loai' => $results->xep_loai,
                    'so_hieu_bang' => $results->so_hieu_bang,
                    'so_so_cap_chung_chi' => $results->so_so_cap_chung_chi,
                    'so_quyet_dinh_tot_nghiep' => $results->so_quyet_dinh_tot_nghiep
                );
                DB::table('hoc_viens')
                        ->where('sbd', $results->sbd)
                        ->where('KhoaThi_Id', $id_khoa_thi)
                        ->update($input);
            }
        });

        // unlink($inputFileName);
    }

}
